package com.googlecode.bip.dao.utils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import javax.naming.NamingException;
import org.apache.log4j.Logger;
import cx.ath.choisnet.sql.ConnectionQuery;

/**
 * Performs some generic tasks on database
 */
public final class DBHelper
{
    private final static transient Logger logger = Logger.getLogger( DBHelper.class );

    private DBHelper()
    {
    }

    /**
     * Build a {@link Timestamp} from a string
     *
     * @param formatter DateFormat for parsing string date.
     * @param date      string date
     * @return a Timestamp
     * @throws ParseException if 'date' could not be decoded according to 'format'
     * @throws NullPointerException if one parameter is null
     * @see SimpleDateFormat
     */
    public static Timestamp toTimestamp(
            final DateFormat    formatter,
            final String        date
            )
        throws ParseException, NullPointerException
    {
        if( date == null ) {
            throw new NullPointerException();
            }

        java.util.Date jdate = formatter.parse( date );

        return new Timestamp( jdate.getTime() );
    }

    /**
     * Build a {@link Timestamp} from a string
     *
     * @param format    format for parsing string date.
     * @param date      string date
     * @return a Timestamp
     * @throws ParseException if 'date' could not be decoded according to 'format'
     * @throws NullPointerException if one parameter is null
     * @see SimpleDateFormat
     */
    public static Timestamp toTimestamp(
            final String format,
            final String date
            )
        throws ParseException, NullPointerException
    {
        return toTimestamp( new SimpleDateFormat( format ), date);
    }

    /**
     * Build a {@link Timestamp} from a string
     *
     * @param date string date, format should be "yyyy-MM-dd hh:mm:ss.S"
     * @return a Timestamp
     * @throws ParseException if 'date' could not be decoded according to 'format'
     * @see #toTimestamp(String, String)
     * @throws NullPointerException if 'date' parameter is null
     */
    public static Timestamp toTimestamp(
            final String date
            )
        throws ParseException, NullPointerException
    {
        return toTimestamp( new SimpleDateFormat( "yyyy-MM-dd hh:mm:ss.S" ), date );
    }

    /**
     * Return a {@link List} of {@link Integer} build using SQL query
     *
     * @param columnLabel    Column name to use to get Integer entries.
     * @param connection    {@link Connection} to use for SQL request
     * @param sqlQuery      SQL query to perform
     * @return a {@link List} of {@link Integer}
     * @throws SQLException if any
     */
    public static List<Integer> getListOfInteger(
            final String        columnLabel,
            final Connection    connection,
            final String        sqlQuery
            )
        throws SQLException
    {
        return getList(
                new ListableInstance<Integer>()
                {
                    @Override
                    public Integer newInstance( ResultSet resultSet )
                            throws SQLException
                    {
                        return new Integer( resultSet.getInt( columnLabel ));
                    }
                },
                connection,
                sqlQuery
                );
    }

    /**
     * Return a {@link List} of T build using SQL query
     *
     * @param <T>           Type for list values
     * @param listable      {@link ListableInstance} to use to create list entries.
     * @param connection    {@link Connection} to use for SQL request
     * @param sqlQuery      SQL query to perform
     * @return a {@link List} of T
     * @throws SQLException if any
     */
    public static <T> List<T> getList(
            final ListableInstance<T>   listable,
            final Connection            connection,
            final String                sqlQuery
            )
        throws SQLException
    {
        final ConnectionQuery query = new ConnectionQuery( connection );

        try {
            if( logger.isDebugEnabled() ) {
                logger.info( "*** getList [" + sqlQuery + "]" );
                }

            try {
                ResultSet   rSet    = query.executeQuery( sqlQuery );
                List<T>     list    = new ArrayList<T>();

                while( rSet.next() ) {
                    list.add( listable.newInstance( rSet ) );
                    }

                rSet.close();

                return list;
            }
            catch( SQLException e ) {
                logger.error(
                        "*** getList [SQLException] for [" + sqlQuery + "]",
                        e
                        );
                throw e;
                }
            }
        finally {
            query.quietClose();
            }
    }

    /**
     * Return an item of type T using SQL query
     *
     * @param <T>       Type of item
     * @param listable  {@link ListableGetter} to use to perform SQL queyr
     * @param sqlQuery  SQL query to perform
     * @return an item of type T if found, null otherwise.
     * @throws SQLException if any
     * @throws NamingException if any
     */
    public static <T> T get(
            final ListableGetter<T> listable,
            final String            sqlQuery
            )
        throws SQLException, NamingException
    {
        List<T> l = listable.getList( sqlQuery );

        if( l.size() != 1 ) {
            if( l.size() > 1 ) { // Inconsistent database
                logger.error(
                    "*** getFromList [Inconsistent database]: return more than one entry ("
                        + l.size() +") for [" + sqlQuery + "]"
                    );
                }
            else {
                if( logger.isDebugEnabled() ) {
                    logger.debug( "*** getFromList no value for [" + sqlQuery + "]" );
                    }
                }

            return null;
            }

        return l.get( 0 );
    }

    /**
     * Load a {@link Map} using SQL query
     *
     * @param <K>           Type for keys
     * @param <V>           Type for values
     * @param mappable      {@link MappableInstance} to use to create map entries.
     * @param connection    {@link Connection} to use for SQL request
     * @param sql           SQL query to use
     * @return a {@link Map} with (key,value) found using sqlQuery
     * @throws SQLException if any
     */
    public static <K,V> Map<K,V> getMap(
            final MappableInstance<K,V> mappable,
            final Connection            connection,
            final String                sql
            )
        throws SQLException
    {
        final ConnectionQuery query = new ConnectionQuery( connection );

        try {
            if( logger.isDebugEnabled() ) {
                logger.info( "*** getMap [" + sql + "]" );
                }

            try {
                ResultSet   rSet    = query.executeQuery( sql );
                Map<K,V>    map     = new LinkedHashMap<K,V>(); // keep order

                while( rSet.next() ) {
                    map.put(
                        mappable.newKey( rSet ),
                        mappable.newValue( rSet )
                        );
                    }

                rSet.close();

                return map;
            }
            catch( SQLException e ) {
                logger.error(
                        "*** getMap [SQLException] for [" + sql + "]",
                        e
                        );
                throw e;
                }
            }
        finally {
            query.quietClose();
            }
    }

    /**
     * Load a {@link Map} of key type of {@link String} and value type of {@link String} using SQL query
     *
     * @param connection        {@link Connection} to use for SQL request
     * @param sql               SQL query to use
     * @param keyFieldName      column name for key
     * @param valueFieldName    column name for value
     * @return a {@link Map} with (key,value) found using sqlQuery
     * @throws SQLException if any
     */
    public static Map<String,String> getStringStringMap(
            final Connection    connection,
            final String        sql,
            final String        keyFieldName,
            final String        valueFieldName
            )
        throws SQLException
    {
        return getMap(
            new MappableInstance<String,String>()
            {
                @Override
                public String newKey( ResultSet resultSet ) throws SQLException
                {
                    return resultSet.getString( keyFieldName );
                }
                @Override
                public String newValue( ResultSet resultSet ) throws SQLException
                {
                    return resultSet.getString( valueFieldName );
                }
            },
            connection,
            sql
            );
    }

}
